package lab03.test13;

import java.sql.*;

public class Test13 {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;


        String sql1 = "insert into t_students(stu_no,real_name,age,gender,department) " +
                "select '13131','张三',20,1,'信工系' UNION " +
                "select '13132','李四',20,1,'信工系' UNION " +
                "select '13133','王五',20,1,'信工系' UNION " +
                "select '13134','赵六',20,1,'信工系' UNION " +
                "select '13135','陈七',20,1,'信工系' UNION " +
                "select '13136','邱八',20,1,'信工系' UNION " +
                "select '13137','黄九',20,1,'信工系' UNION " +
                "select '13138','赌石',20,1,'信工系' UNION " +
                "select '13139','落一',20,1,'信工系' UNION " +
                "select '131310','小蝌蚪',20,1,'信工系'";

        String sql2 = "select * from t_students";

        String sql3 = "delete from t_students where id=5";

        String sql4 = "update t_students set department='信息与工程学院' where id=3";

        try {
            //会抛出错误
            //加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            //数据库连接字符串：jdbc:mysql:// + ip地址 + :3306/数据库地址
            //会抛出错误
            connection = DriverManager.getConnection(
                    //?serverTimezone=UTC实区
                    "jdbc:mysql://localhost:3306/java_2017?serverTimezone=UTC&characterEncoding=utf-8",
                    "root",
                    "123456");

            statement = connection.createStatement();

            //ResultSet：只有在“查”时用到，只有查才会有结果集。

//            3)	向数据库中插入十条示例数据；
            statement.execute(sql1);

//            4)	打印学生表的所有学生信息。
            resultSet = statement.executeQuery(sql2);
            while (resultSet.next()){
                System.out.print(resultSet.getInt(1) + "\t");
                System.out.print(resultSet.getString(2) + "\t");
                System.out.print(resultSet.getString(3) + "\t");
                System.out.print(resultSet.getInt(4) + "\t");
                System.out.print(resultSet.getInt(5) + "\t");
                System.out.print(resultSet.getString(6) + "\t");
                System.out.println();
            }

//            5)	删除 id 为5的学生信息；
            statement.execute(sql3);

//            6)	查找 id 为3的学生并将其院系更改为“信息与工程学院”。
            statement.execute(sql4);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                //关闭文件
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }
}
